{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.model_selection import train_test_split, KFold\n",
    "\n",
    "from sklearn.ensemble import RandomForestClassifier, GradientBoostingClassifier\n",
    "from sklearn.tree import DecisionTreeClassifier, ExtraTreeClassifier\n",
    "from sklearn.metrics import accuracy_score\n",
    "\n",
    "import xgboost as xgb\n",
    "\n",
    "import lightgbm as lgb\n",
    "#os.chdir('C:/Users/BTHANISH/Documents/Thanish/Competition/Machine hack/Predicting Food Delivery Time')\n",
    "os.chdir('E:/Thanish/Data science/Machine Hack/Predicting Food Delivery Time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11094, 9) (2774, 9)\n"
     ]
    }
   ],
   "source": [
    "DF_train = pd.read_excel('Data_Train.xlsx')\n",
    "DF_test= pd.read_excel('Data_Test.xlsx')\n",
    "\n",
    "#Combining train and test\n",
    "DF_test['Delivery_Time'] = None\n",
    "DF_prod = pd.concat([DF_train, DF_test]).reset_index(drop = True)\n",
    "\n",
    "print(DF_train.shape, DF_test.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Restaurant</th>\n",
       "      <th>Location</th>\n",
       "      <th>Cuisines</th>\n",
       "      <th>Average_Cost</th>\n",
       "      <th>Minimum_Order</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Votes</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Delivery_Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID_6321</td>\n",
       "      <td>FTI College, Law College Road, Pune</td>\n",
       "      <td>Fast Food, Rolls, Burger, Salad, Wraps</td>\n",
       "      <td>₹200</td>\n",
       "      <td>₹50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>30 minutes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID_2882</td>\n",
       "      <td>Sector 3, Marathalli</td>\n",
       "      <td>Ice Cream, Desserts</td>\n",
       "      <td>₹100</td>\n",
       "      <td>₹50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>30 minutes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID_1595</td>\n",
       "      <td>Mumbai Central</td>\n",
       "      <td>Italian, Street Food, Fast Food</td>\n",
       "      <td>₹150</td>\n",
       "      <td>₹50</td>\n",
       "      <td>3.6</td>\n",
       "      <td>99</td>\n",
       "      <td>30</td>\n",
       "      <td>65 minutes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID_5929</td>\n",
       "      <td>Sector 1, Noida</td>\n",
       "      <td>Mughlai, North Indian, Chinese</td>\n",
       "      <td>₹250</td>\n",
       "      <td>₹99</td>\n",
       "      <td>3.7</td>\n",
       "      <td>176</td>\n",
       "      <td>95</td>\n",
       "      <td>30 minutes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID_6123</td>\n",
       "      <td>Rmz Centennial, I Gate, Whitefield</td>\n",
       "      <td>Cafe, Beverages</td>\n",
       "      <td>₹200</td>\n",
       "      <td>₹99</td>\n",
       "      <td>3.2</td>\n",
       "      <td>521</td>\n",
       "      <td>235</td>\n",
       "      <td>65 minutes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Restaurant                             Location  \\\n",
       "0    ID_6321  FTI College, Law College Road, Pune   \n",
       "1    ID_2882                 Sector 3, Marathalli   \n",
       "2    ID_1595                       Mumbai Central   \n",
       "3    ID_5929                      Sector 1, Noida   \n",
       "4    ID_6123   Rmz Centennial, I Gate, Whitefield   \n",
       "\n",
       "                                 Cuisines Average_Cost Minimum_Order Rating  \\\n",
       "0  Fast Food, Rolls, Burger, Salad, Wraps         ₹200           ₹50    3.5   \n",
       "1                     Ice Cream, Desserts         ₹100           ₹50    3.5   \n",
       "2         Italian, Street Food, Fast Food         ₹150           ₹50    3.6   \n",
       "3          Mughlai, North Indian, Chinese         ₹250           ₹99    3.7   \n",
       "4                         Cafe, Beverages         ₹200           ₹99    3.2   \n",
       "\n",
       "  Votes Reviews Delivery_Time  \n",
       "0    12       4    30 minutes  \n",
       "1    11       4    30 minutes  \n",
       "2    99      30    65 minutes  \n",
       "3   176      95    30 minutes  \n",
       "4   521     235    65 minutes  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DF_prod.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13868, 9)\n",
      "(13865, 9)\n"
     ]
    }
   ],
   "source": [
    "#Dropping few rows\n",
    "print(DF_prod.shape)\n",
    "DF_prod = DF_prod.loc[DF_prod['Average_Cost'] != 'for',:]\n",
    "DF_prod = DF_prod.loc[DF_prod['Rating'] != 'Temporarily Closed',:]\n",
    "print(DF_prod.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Restaurant       object\n",
      "Location         object\n",
      "Cuisines         object\n",
      "Average_Cost      int32\n",
      "Minimum_Order     int32\n",
      "Rating           object\n",
      "Votes            object\n",
      "Reviews          object\n",
      "Delivery_Time    object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Replacing the rupees \n",
    "DF_prod['Average_Cost'] = DF_prod.Average_Cost.str.replace('₹|,', '').astype('int')\n",
    "DF_prod['Minimum_Order'] = DF_prod.Minimum_Order.str.replace('₹|,', '').astype('int')\n",
    "print(DF_prod.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Bellagio\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"\n",
      "C:\\Users\\Bellagio\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n",
      "C:\\Users\\Bellagio\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:12: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  if sys.path[0] == '':\n"
     ]
    }
   ],
   "source": [
    "DF_prod['restaurant_type'] = np.where(DF_prod.Rating == '-', 3, \n",
    "                                     np.where(DF_prod.Rating == 'Opening Soon', 2, \n",
    "                                             np.where(DF_prod.Rating == 'NEW', 1, 0)))\n",
    "\n",
    "DF_prod.Rating[DF_prod.Rating.isin(['-', 'Opening Soon', 'NEW'])] = -999\n",
    "DF_prod.Rating = DF_prod.Rating.astype('float')\n",
    "\n",
    "DF_prod.Votes[DF_prod.Votes.isin(['-'])] = -999\n",
    "DF_prod.Votes = DF_prod.Votes.astype('float')\n",
    "\n",
    "DF_prod.Reviews[DF_prod.Reviews.isin(['-'])] = -999\n",
    "DF_prod.Reviews = DF_prod.Reviews.astype('float')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Restaurant</th>\n",
       "      <th>Location</th>\n",
       "      <th>Cuisines</th>\n",
       "      <th>Average_Cost</th>\n",
       "      <th>Minimum_Order</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Votes</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Delivery_Time</th>\n",
       "      <th>restaurant_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID_6321</td>\n",
       "      <td>FTI College, Law College Road, Pune</td>\n",
       "      <td>Fast Food, Rolls, Burger, Salad, Wraps</td>\n",
       "      <td>200</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID_2882</td>\n",
       "      <td>Sector 3, Marathalli</td>\n",
       "      <td>Ice Cream, Desserts</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID_1595</td>\n",
       "      <td>Mumbai Central</td>\n",
       "      <td>Italian, Street Food, Fast Food</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>3.6</td>\n",
       "      <td>99.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>65 minutes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID_5929</td>\n",
       "      <td>Sector 1, Noida</td>\n",
       "      <td>Mughlai, North Indian, Chinese</td>\n",
       "      <td>250</td>\n",
       "      <td>99</td>\n",
       "      <td>3.7</td>\n",
       "      <td>176.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID_6123</td>\n",
       "      <td>Rmz Centennial, I Gate, Whitefield</td>\n",
       "      <td>Cafe, Beverages</td>\n",
       "      <td>200</td>\n",
       "      <td>99</td>\n",
       "      <td>3.2</td>\n",
       "      <td>521.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>65 minutes</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Restaurant                             Location  \\\n",
       "0    ID_6321  FTI College, Law College Road, Pune   \n",
       "1    ID_2882                 Sector 3, Marathalli   \n",
       "2    ID_1595                       Mumbai Central   \n",
       "3    ID_5929                      Sector 1, Noida   \n",
       "4    ID_6123   Rmz Centennial, I Gate, Whitefield   \n",
       "\n",
       "                                 Cuisines  Average_Cost  Minimum_Order  \\\n",
       "0  Fast Food, Rolls, Burger, Salad, Wraps           200             50   \n",
       "1                     Ice Cream, Desserts           100             50   \n",
       "2         Italian, Street Food, Fast Food           150             50   \n",
       "3          Mughlai, North Indian, Chinese           250             99   \n",
       "4                         Cafe, Beverages           200             99   \n",
       "\n",
       "   Rating  Votes  Reviews Delivery_Time  restaurant_type  \n",
       "0     3.5   12.0      4.0    30 minutes                0  \n",
       "1     3.5   11.0      4.0    30 minutes                0  \n",
       "2     3.6   99.0     30.0    65 minutes                0  \n",
       "3     3.7  176.0     95.0    30 minutes                0  \n",
       "4     3.2  521.0    235.0    65 minutes                0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Resetting the index\n",
    "DF_prod = DF_prod.reset_index(drop = True)\n",
    "DF_prod.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# votes, reviews, rating multiple\n",
    "DF_prod['votes_review_rating'] = DF_prod.Votes*DF_prod.Reviews*DF_prod.Rating\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Restaurant</th>\n",
       "      <th>Location</th>\n",
       "      <th>Cuisines</th>\n",
       "      <th>Average_Cost</th>\n",
       "      <th>Minimum_Order</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Votes</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Delivery_Time</th>\n",
       "      <th>restaurant_type</th>\n",
       "      <th>votes_review_rating</th>\n",
       "      <th>Cusine_len</th>\n",
       "      <th>new_city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID_6321</td>\n",
       "      <td>FTI College, Law College Road, Pune</td>\n",
       "      <td>Fast Food, Rolls, Burger, Salad, Wraps</td>\n",
       "      <td>200</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>5</td>\n",
       "      <td>Pune</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID_2882</td>\n",
       "      <td>Sector 3, Marathalli</td>\n",
       "      <td>Ice Cream, Desserts</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Banglore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID_1595</td>\n",
       "      <td>Mumbai Central</td>\n",
       "      <td>Italian, Street Food, Fast Food</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>3.6</td>\n",
       "      <td>99.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>65 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>10692.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Mumbai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID_5929</td>\n",
       "      <td>Sector 1, Noida</td>\n",
       "      <td>Mughlai, North Indian, Chinese</td>\n",
       "      <td>250</td>\n",
       "      <td>99</td>\n",
       "      <td>3.7</td>\n",
       "      <td>176.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>61864.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Noida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID_6123</td>\n",
       "      <td>Rmz Centennial, I Gate, Whitefield</td>\n",
       "      <td>Cafe, Beverages</td>\n",
       "      <td>200</td>\n",
       "      <td>99</td>\n",
       "      <td>3.2</td>\n",
       "      <td>521.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>65 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>391792.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Banglore</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Restaurant                             Location  \\\n",
       "0    ID_6321  FTI College, Law College Road, Pune   \n",
       "1    ID_2882                 Sector 3, Marathalli   \n",
       "2    ID_1595                       Mumbai Central   \n",
       "3    ID_5929                      Sector 1, Noida   \n",
       "4    ID_6123   Rmz Centennial, I Gate, Whitefield   \n",
       "\n",
       "                                 Cuisines  Average_Cost  Minimum_Order  \\\n",
       "0  Fast Food, Rolls, Burger, Salad, Wraps           200             50   \n",
       "1                     Ice Cream, Desserts           100             50   \n",
       "2         Italian, Street Food, Fast Food           150             50   \n",
       "3          Mughlai, North Indian, Chinese           250             99   \n",
       "4                         Cafe, Beverages           200             99   \n",
       "\n",
       "   Rating  Votes  Reviews Delivery_Time  restaurant_type  votes_review_rating  \\\n",
       "0     3.5   12.0      4.0    30 minutes                0                168.0   \n",
       "1     3.5   11.0      4.0    30 minutes                0                154.0   \n",
       "2     3.6   99.0     30.0    65 minutes                0              10692.0   \n",
       "3     3.7  176.0     95.0    30 minutes                0              61864.0   \n",
       "4     3.2  521.0    235.0    65 minutes                0             391792.0   \n",
       "\n",
       "   Cusine_len  new_city  \n",
       "0           5      Pune  \n",
       "1           2  Banglore  \n",
       "2           3    Mumbai  \n",
       "3           3     Noida  \n",
       "4           2  Banglore  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DF_prod['Cusine_len'] = DF_prod.Cuisines.apply(lambda x : len(x.replace(\" \", \" \").split(\",\")))\n",
    "\n",
    "DF_prod['city'] = DF_prod['Location'].apply(lambda x : np.char.strip(x.split(','))[-1])\n",
    "actual_city = {'Noida' : 'Noida', \n",
    "               'Gurgaon' : 'Gurgoan',\n",
    "               'Gurgoan' : 'Gurgoan',\n",
    "               'Mumbai CST Area' : 'Mumbai',\n",
    "               'Mumbai Central' : 'Mumbai',\n",
    "               'Mumbai' : 'Mumbai',\n",
    "               'Pune' : 'Pune',\n",
    "               'Maharashtra' : 'Pune',\n",
    "               'Pune University' : 'Pune',\n",
    "               'Timarpur' : 'Delhi',\n",
    "               'Delhi' : 'Delhi',\n",
    "               'Delhi Cantt.' : 'Delhi',\n",
    "               'Delhi University-GTB Nagar' : 'Delhi',\n",
    "               'India Gate' : 'Delhi',\n",
    "               'Whitefield' : 'Banglore', \n",
    "               'Marathalli' : 'Banglore',\n",
    "               'Majestic' : 'Banglore',\n",
    "               'Bangalore' : 'Banglore',\n",
    "               'Electronic City' : 'Banglore',\n",
    "               'Hyderabad' : 'Hyderabad',\n",
    "               'Begumpet' : 'Hyderabad',\n",
    "               'Kolkata' : 'Kolkata'\n",
    "               }\n",
    "\n",
    "DF_prod['new_city'] = DF_prod[['city']].applymap(actual_city.get)\n",
    "DF_prod.drop(['city'], axis = 1, inplace = True)\n",
    "\n",
    "DF_prod.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Restaurant</th>\n",
       "      <th>Location</th>\n",
       "      <th>Cuisines</th>\n",
       "      <th>Average_Cost</th>\n",
       "      <th>Minimum_Order</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Votes</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Delivery_Time</th>\n",
       "      <th>restaurant_type</th>\n",
       "      <th>votes_review_rating</th>\n",
       "      <th>Cusine_len</th>\n",
       "      <th>new_city</th>\n",
       "      <th>avg_restaurant_Rating</th>\n",
       "      <th>median_restaurant_Rating</th>\n",
       "      <th>avg_restaurant_Reviews</th>\n",
       "      <th>median_restaurant_Reviews</th>\n",
       "      <th>avg_restaurant_Votes</th>\n",
       "      <th>median_restaurant_Votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID_6321</td>\n",
       "      <td>FTI College, Law College Road, Pune</td>\n",
       "      <td>Fast Food, Rolls, Burger, Salad, Wraps</td>\n",
       "      <td>200</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>5</td>\n",
       "      <td>Pune</td>\n",
       "      <td>3.5000</td>\n",
       "      <td>3.5</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.00</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID_2882</td>\n",
       "      <td>Sector 3, Marathalli</td>\n",
       "      <td>Ice Cream, Desserts</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Banglore</td>\n",
       "      <td>3.1250</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.25</td>\n",
       "      <td>11.0</td>\n",
       "      <td>19.25</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID_1595</td>\n",
       "      <td>Mumbai Central</td>\n",
       "      <td>Italian, Street Food, Fast Food</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>3.6</td>\n",
       "      <td>99.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>65 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>10692.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>3.6000</td>\n",
       "      <td>3.6</td>\n",
       "      <td>30.00</td>\n",
       "      <td>30.0</td>\n",
       "      <td>99.00</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID_5929</td>\n",
       "      <td>Sector 1, Noida</td>\n",
       "      <td>Mughlai, North Indian, Chinese</td>\n",
       "      <td>250</td>\n",
       "      <td>99</td>\n",
       "      <td>3.7</td>\n",
       "      <td>176.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>61864.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Noida</td>\n",
       "      <td>3.7000</td>\n",
       "      <td>3.7</td>\n",
       "      <td>95.00</td>\n",
       "      <td>95.0</td>\n",
       "      <td>176.00</td>\n",
       "      <td>176.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID_6123</td>\n",
       "      <td>Rmz Centennial, I Gate, Whitefield</td>\n",
       "      <td>Cafe, Beverages</td>\n",
       "      <td>200</td>\n",
       "      <td>99</td>\n",
       "      <td>3.2</td>\n",
       "      <td>521.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>65 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>391792.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Banglore</td>\n",
       "      <td>3.2000</td>\n",
       "      <td>3.2</td>\n",
       "      <td>235.00</td>\n",
       "      <td>235.0</td>\n",
       "      <td>521.00</td>\n",
       "      <td>521.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>ID_3468</td>\n",
       "      <td>D-Block, Sector 63, Noida</td>\n",
       "      <td>Fast Food</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>3.3</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>171.6</td>\n",
       "      <td>1</td>\n",
       "      <td>Noida</td>\n",
       "      <td>3.3000</td>\n",
       "      <td>3.3</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.00</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>ID_3797</td>\n",
       "      <td>Delhi High Court, India Gate</td>\n",
       "      <td>Fast Food</td>\n",
       "      <td>200</td>\n",
       "      <td>50</td>\n",
       "      <td>3.3</td>\n",
       "      <td>25.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>3.3000</td>\n",
       "      <td>3.3</td>\n",
       "      <td>16.00</td>\n",
       "      <td>16.0</td>\n",
       "      <td>25.00</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>ID_6224</td>\n",
       "      <td>D-Block, Sector 63, Noida</td>\n",
       "      <td>Chinese</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Noida</td>\n",
       "      <td>3.5000</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>24.00</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>ID_1124</td>\n",
       "      <td>Delhi University-GTB Nagar</td>\n",
       "      <td>Burger, Fast Food</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>3</td>\n",
       "      <td>-997002999.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>-999.0000</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.00</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.00</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>ID_6637</td>\n",
       "      <td>Delhi Cantt.</td>\n",
       "      <td>Mughlai</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>2.8</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30 minutes</td>\n",
       "      <td>0</td>\n",
       "      <td>58.8</td>\n",
       "      <td>1</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>3.2625</td>\n",
       "      <td>3.2</td>\n",
       "      <td>6.25</td>\n",
       "      <td>2.0</td>\n",
       "      <td>22.50</td>\n",
       "      <td>9.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Restaurant                             Location  \\\n",
       "0     ID_6321  FTI College, Law College Road, Pune   \n",
       "1     ID_2882                 Sector 3, Marathalli   \n",
       "2     ID_1595                       Mumbai Central   \n",
       "3     ID_5929                      Sector 1, Noida   \n",
       "4     ID_6123   Rmz Centennial, I Gate, Whitefield   \n",
       "..        ...                                  ...   \n",
       "95    ID_3468            D-Block, Sector 63, Noida   \n",
       "96    ID_3797         Delhi High Court, India Gate   \n",
       "97    ID_6224            D-Block, Sector 63, Noida   \n",
       "98    ID_1124           Delhi University-GTB Nagar   \n",
       "99    ID_6637                         Delhi Cantt.   \n",
       "\n",
       "                                  Cuisines  Average_Cost  Minimum_Order  \\\n",
       "0   Fast Food, Rolls, Burger, Salad, Wraps           200             50   \n",
       "1                      Ice Cream, Desserts           100             50   \n",
       "2          Italian, Street Food, Fast Food           150             50   \n",
       "3           Mughlai, North Indian, Chinese           250             99   \n",
       "4                          Cafe, Beverages           200             99   \n",
       "..                                     ...           ...            ...   \n",
       "95                               Fast Food           100             50   \n",
       "96                               Fast Food           200             50   \n",
       "97                                 Chinese           100             50   \n",
       "98                       Burger, Fast Food           100             50   \n",
       "99                                 Mughlai           150             50   \n",
       "\n",
       "    Rating  Votes  Reviews Delivery_Time  restaurant_type  \\\n",
       "0      3.5   12.0      4.0    30 minutes                0   \n",
       "1      3.5   11.0      4.0    30 minutes                0   \n",
       "2      3.6   99.0     30.0    65 minutes                0   \n",
       "3      3.7  176.0     95.0    30 minutes                0   \n",
       "4      3.2  521.0    235.0    65 minutes                0   \n",
       "..     ...    ...      ...           ...              ...   \n",
       "95     3.3   13.0      4.0    30 minutes                0   \n",
       "96     3.3   25.0     16.0    30 minutes                0   \n",
       "97     3.5   24.0      1.0    30 minutes                0   \n",
       "98  -999.0 -999.0   -999.0    30 minutes                3   \n",
       "99     2.8    7.0      3.0    30 minutes                0   \n",
       "\n",
       "    votes_review_rating  Cusine_len  new_city  avg_restaurant_Rating  \\\n",
       "0                 168.0           5      Pune                 3.5000   \n",
       "1                 154.0           2  Banglore                 3.1250   \n",
       "2               10692.0           3    Mumbai                 3.6000   \n",
       "3               61864.0           3     Noida                 3.7000   \n",
       "4              391792.0           2  Banglore                 3.2000   \n",
       "..                  ...         ...       ...                    ...   \n",
       "95                171.6           1     Noida                 3.3000   \n",
       "96               1320.0           1     Delhi                 3.3000   \n",
       "97                 84.0           1     Noida                 3.5000   \n",
       "98         -997002999.0           2     Delhi              -999.0000   \n",
       "99                 58.8           1     Delhi                 3.2625   \n",
       "\n",
       "    median_restaurant_Rating  avg_restaurant_Reviews  \\\n",
       "0                        3.5                    4.00   \n",
       "1                        3.0                    9.25   \n",
       "2                        3.6                   30.00   \n",
       "3                        3.7                   95.00   \n",
       "4                        3.2                  235.00   \n",
       "..                       ...                     ...   \n",
       "95                       3.3                    4.00   \n",
       "96                       3.3                   16.00   \n",
       "97                       3.5                    1.00   \n",
       "98                    -999.0                 -999.00   \n",
       "99                       3.2                    6.25   \n",
       "\n",
       "    median_restaurant_Reviews  avg_restaurant_Votes  median_restaurant_Votes  \n",
       "0                         4.0                 12.00                     12.0  \n",
       "1                        11.0                 19.25                     22.0  \n",
       "2                        30.0                 99.00                     99.0  \n",
       "3                        95.0                176.00                    176.0  \n",
       "4                       235.0                521.00                    521.0  \n",
       "..                        ...                   ...                      ...  \n",
       "95                        4.0                 13.00                     13.0  \n",
       "96                       16.0                 25.00                     25.0  \n",
       "97                        1.0                 24.00                     24.0  \n",
       "98                     -999.0               -999.00                   -999.0  \n",
       "99                        2.0                 22.50                      9.5  \n",
       "\n",
       "[100 rows x 19 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Adding average restaurant data\n",
    "Average_restaurant_rating = DF_prod.loc[DF_prod.Rating != -999 ,:].groupby('Restaurant')['Rating'].agg([('avg_restaurant_Rating', 'mean')\n",
    "                                                                                                        ,('median_restaurant_Rating', 'median')\n",
    "                                                                                                        ]).reset_index()\n",
    "Average_restaurant_Review = DF_prod.loc[DF_prod.Rating != -999 ,:].groupby('Restaurant')['Reviews'].agg([('avg_restaurant_Reviews', 'mean')\n",
    "                                                                                                         ,('median_restaurant_Reviews', 'median'),\n",
    "                                                                                                         ]).reset_index()\n",
    "Average_restaurant_Votes = DF_prod.loc[DF_prod.Rating != -999 ,:].groupby('Restaurant')['Votes'].agg([('avg_restaurant_Votes', 'mean')\n",
    "                                                                                                      ,('median_restaurant_Votes', 'median')\n",
    "                                                                                                      ]).reset_index()\n",
    "\n",
    "DF_prod = pd.merge(DF_prod, Average_restaurant_rating, how = 'left', left_on = 'Restaurant', right_on = 'Restaurant')\n",
    "DF_prod = pd.merge(DF_prod, Average_restaurant_Review, how = 'left', left_on = 'Restaurant', right_on = 'Restaurant')\n",
    "DF_prod = pd.merge(DF_prod, Average_restaurant_Votes, how = 'left', left_on = 'Restaurant', right_on = 'Restaurant')\n",
    "\n",
    "columns = ['avg_restaurant_Rating', 'avg_restaurant_Reviews', 'avg_restaurant_Votes'\n",
    "           ,'median_restaurant_Rating', 'median_restaurant_Reviews', 'median_restaurant_Votes'\n",
    "           ]\n",
    "\n",
    "DF_prod[columns] = DF_prod[columns].fillna(-999)\n",
    "           \n",
    "DF_prod.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Total restaurant branches\n",
    "DF_prod['total_branches'] = DF_prod.groupby(['Restaurant']).new_city.transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Afghan</th>\n",
       "      <th>African</th>\n",
       "      <th>American</th>\n",
       "      <th>Andhra</th>\n",
       "      <th>Arabian</th>\n",
       "      <th>Asian</th>\n",
       "      <th>Assamese</th>\n",
       "      <th>Awadhi</th>\n",
       "      <th>BBQ</th>\n",
       "      <th>Bakery</th>\n",
       "      <th>...</th>\n",
       "      <th>Street Food</th>\n",
       "      <th>Sushi</th>\n",
       "      <th>Tamil</th>\n",
       "      <th>Tea</th>\n",
       "      <th>Tex-Mex</th>\n",
       "      <th>Thai</th>\n",
       "      <th>Tibetan</th>\n",
       "      <th>Turkish</th>\n",
       "      <th>Vietnamese</th>\n",
       "      <th>Wraps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13860</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13861</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13862</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13863</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13864</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 101 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Afghan  African  American  Andhra  Arabian  Asian  Assamese  Awadhi  \\\n",
       "13860       0        0         0       0        0      0         0       0   \n",
       "13861       0        0         0       0        0      0         0       0   \n",
       "13862       0        0         0       1        0      0         0       0   \n",
       "13863       0        0         0       0        0      0         0       0   \n",
       "13864       0        0         0       0        0      0         0       0   \n",
       "\n",
       "       BBQ  Bakery  ...  Street Food  Sushi  Tamil  Tea  Tex-Mex  Thai  \\\n",
       "13860    0       0  ...            0      0      0    0        0     0   \n",
       "13861    0       0  ...            0      0      0    0        0     0   \n",
       "13862    0       0  ...            0      0      0    0        0     0   \n",
       "13863    0       1  ...            0      0      0    0        0     0   \n",
       "13864    0       0  ...            1      0      0    0        0     0   \n",
       "\n",
       "       Tibetan  Turkish  Vietnamese  Wraps  \n",
       "13860        0        0           0      0  \n",
       "13861        0        0           0      0  \n",
       "13862        0        0           0      0  \n",
       "13863        0        0           0      0  \n",
       "13864        0        0           0      0  \n",
       "\n",
       "[5 rows x 101 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating one hot encoding of the cuisines\n",
    "\n",
    "available_cuisines = list(DF_prod.Cuisines.apply(lambda x : x.split(\",\")))\n",
    "merged = list(itertools.chain.from_iterable(available_cuisines))\n",
    "merged = np.sort(np.unique(np.char.lstrip(merged)))\n",
    "print(merged)\n",
    "\n",
    "cuisine_DF = pd.DataFrame(0, index=np.arange(len(DF_prod)), columns = merged)\n",
    "print(cuisine_DF.shape)\n",
    "cuisine_DF.head()\n",
    "\n",
    "# Filling the Cusines OHE columns\n",
    "for i in range(len(DF_prod)):    \n",
    "    print(i)\n",
    "    cuisine_list = DF_prod.Cuisines[i:(i+1)].apply(lambda x : np.char.strip(x.split(\",\"))).tolist()\n",
    "    cuisine_list = cuisine_list[0]\n",
    "    \n",
    "    for cuisine in cuisine_list:\n",
    "        cuisine_DF.loc[i,cuisine] = 1\n",
    "        \n",
    "# cuisine_DF.to_csv('cuisine_DF.csv', index = False)\n",
    "\n",
    "# cuisine_DF = pd.read_csv('cuisine_DF.csv')\n",
    "cuisine_DF.rename(columns = {'Poké' : 'Poke'}, inplace = True)\n",
    "cuisine_DF.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13865, 20)\n",
      "(13865, 121)\n"
     ]
    }
   ],
   "source": [
    "# Merging the cuisines OHE to the main dataframe\n",
    "\n",
    "print(DF_prod.shape)\n",
    "DF_prod = pd.merge(DF_prod, cuisine_DF, left_index=True, right_index=True)\n",
    "print(DF_prod.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Cuisines', 'Location', 'Restaurant', 'new_city'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# Separating the numerical and categorical columns\n",
    "\n",
    "dep_column = 'Delivery_Time'\n",
    "\n",
    "cat_columns = DF_prod.select_dtypes(include = ['object']).columns.difference([dep_column])\n",
    "int_columns = DF_prod.select_dtypes(include = ['int32']).columns\n",
    "\n",
    "print(cat_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Convert cat columns to numerics\n",
    "LE = LabelEncoder()\n",
    "DF_prod[cat_columns] = DF_prod[cat_columns].apply(lambda x : LE.fit_transform(x))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11091, 121) (2774, 121)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Bellagio\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Restaurant</th>\n",
       "      <th>Location</th>\n",
       "      <th>Cuisines</th>\n",
       "      <th>Average_Cost</th>\n",
       "      <th>Minimum_Order</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Votes</th>\n",
       "      <th>Reviews</th>\n",
       "      <th>Delivery_Time</th>\n",
       "      <th>restaurant_type</th>\n",
       "      <th>...</th>\n",
       "      <th>Street Food</th>\n",
       "      <th>Sushi</th>\n",
       "      <th>Tamil</th>\n",
       "      <th>Tea</th>\n",
       "      <th>Tex-Mex</th>\n",
       "      <th>Thai</th>\n",
       "      <th>Tibetan</th>\n",
       "      <th>Turkish</th>\n",
       "      <th>Vietnamese</th>\n",
       "      <th>Wraps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5914</td>\n",
       "      <td>10</td>\n",
       "      <td>1085</td>\n",
       "      <td>200</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2093</td>\n",
       "      <td>30</td>\n",
       "      <td>1215</td>\n",
       "      <td>100</td>\n",
       "      <td>50</td>\n",
       "      <td>3.5</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>663</td>\n",
       "      <td>19</td>\n",
       "      <td>1287</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>3.6</td>\n",
       "      <td>99.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5478</td>\n",
       "      <td>28</td>\n",
       "      <td>1525</td>\n",
       "      <td>250</td>\n",
       "      <td>99</td>\n",
       "      <td>3.7</td>\n",
       "      <td>176.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5694</td>\n",
       "      <td>26</td>\n",
       "      <td>423</td>\n",
       "      <td>200</td>\n",
       "      <td>99</td>\n",
       "      <td>3.2</td>\n",
       "      <td>521.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 121 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Restaurant  Location  Cuisines  Average_Cost  Minimum_Order  Rating  Votes  \\\n",
       "0        5914        10      1085           200             50     3.5   12.0   \n",
       "1        2093        30      1215           100             50     3.5   11.0   \n",
       "2         663        19      1287           150             50     3.6   99.0   \n",
       "3        5478        28      1525           250             99     3.7  176.0   \n",
       "4        5694        26       423           200             99     3.2  521.0   \n",
       "\n",
       "   Reviews  Delivery_Time  restaurant_type  ...  Street Food  Sushi  Tamil  \\\n",
       "0      4.0              3                0  ...            0      0      0   \n",
       "1      4.0              3                0  ...            0      0      0   \n",
       "2     30.0              5                0  ...            1      0      0   \n",
       "3     95.0              3                0  ...            0      0      0   \n",
       "4    235.0              5                0  ...            0      0      0   \n",
       "\n",
       "   Tea  Tex-Mex  Thai  Tibetan  Turkish  Vietnamese  Wraps  \n",
       "0    0        0     0        0        0           0      1  \n",
       "1    0        0     0        0        0           0      0  \n",
       "2    0        0     0        0        0           0      0  \n",
       "3    0        0     0        0        0           0      0  \n",
       "4    0        0     0        0        0           0      0  \n",
       "\n",
       "[5 rows x 121 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Separating the training and testing data\n",
    "\n",
    "train_prod = DF_prod.loc[~DF_prod.Delivery_Time.isnull(), ]\n",
    "test_prod  = DF_prod.loc[DF_prod.Delivery_Time.isnull(), ]\n",
    "\n",
    "# Label encode the dependent column to reverse encode later\n",
    "Dep_encoder = LabelEncoder()\n",
    "Dep_encoder.fit(train_prod[dep_column])\n",
    "train_prod[dep_column] = Dep_encoder.transform(train_prod[dep_column])\n",
    "\n",
    "print(train_prod.shape, test_prod.shape)\n",
    "train_prod.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Afghan', 'African', 'American', 'Andhra', 'Arabian', 'Asian',\n",
      "       'Assamese', 'Average_Cost', 'Awadhi', 'BBQ',\n",
      "       ...\n",
      "       'Votes', 'Wraps', 'avg_restaurant_Rating', 'avg_restaurant_Reviews',\n",
      "       'avg_restaurant_Votes', 'median_restaurant_Rating',\n",
      "       'median_restaurant_Reviews', 'median_restaurant_Votes', 'new_city',\n",
      "       'restaurant_type'],\n",
      "      dtype='object', length=118)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "((7763, 118), (7763,), (3328, 118), (3328,))"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Splitting the data to local train and test\n",
    "not_in_testset = ['Tex-Mex', 'Tamil', 'Sri Lankan', 'South American', 'Portuguese', \n",
    "                  'Nepalese', 'Konkan', 'Israeli', 'Indonesian', 'Greek', 'Gujarati', \n",
    "                  'Charcoal Chicken', 'Cantonese', 'Bubble Tea','Bohri']\n",
    "\n",
    "indep = DF_prod.columns.difference([dep_column\n",
    "                                    , 'total_branches'\n",
    "                                    , 'Top_restaurant'\n",
    "                                    ,'votes_review_rating'\n",
    "                                   ] \n",
    "                                  )\n",
    "print(indep)\n",
    "\n",
    "np.random.seed(100)\n",
    "train_local_X, test_local_X, train_local_Y, test_local_Y = train_test_split(train_prod[indep], \n",
    "                                                                            train_prod[dep_column],\n",
    "                                                                            test_size = 0.3)\n",
    "\n",
    "train_local_X.shape, train_local_Y.shape, test_local_X.shape, test_local_Y.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Random Forest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Model-1 Validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.8097956730769231\n"
     ]
    }
   ],
   "source": [
    "np.random.seed(100)\n",
    "RF = RandomForestClassifier(n_estimators = 280)\n",
    "RF.fit(train_local_X, train_local_Y)\n",
    "RF_local_prediction = RF.predict(test_local_X)\n",
    "\n",
    "print(accuracy_score(RF_local_prediction, test_local_Y))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Model-1 Final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.seed(100)\n",
    "RF = RandomForestClassifier(n_estimators=280)\n",
    "RF.fit(train_prod[indep], train_prod[dep_column])\n",
    "RF_prod_prediction_9 = RF.predict_proba(test_prod[indep])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RF 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Afghan', 'African', 'American', 'Andhra', 'Arabian', 'Asian',\n",
      "       'Assamese', 'Average_Cost', 'Awadhi', 'BBQ', 'Bakery', 'Bangladeshi',\n",
      "       'Bar Food', 'Belgian', 'Bengali', 'Beverages', 'Bihari', 'Biryani',\n",
      "       'Brazilian', 'Burger', 'Burmese', 'Cafe', 'Chettinad', 'Chinese',\n",
      "       'Coffee', 'Continental', 'Cuisines', 'Desserts', 'European',\n",
      "       'Fast Food', 'Finger Food', 'French', 'Frozen Yogurt', 'German', 'Goan',\n",
      "       'Healthy Food', 'Hot dogs', 'Hyderabadi', 'Ice Cream', 'Indian',\n",
      "       'Iranian', 'Italian', 'Japanese', 'Juices', 'Kashmiri', 'Kebab',\n",
      "       'Kerala', 'Korean', 'Lebanese', 'Lucknowi', 'Maharashtrian',\n",
      "       'Malaysian', 'Malwani', 'Mangalorean', 'Mediterranean', 'Mexican',\n",
      "       'Middle Eastern', 'Minimum_Order', 'Mishti', 'Mithai', 'Modern Indian',\n",
      "       'Momos', 'Mughlai', 'Naga', 'North Eastern', 'North Indian', 'Odia',\n",
      "       'Paan', 'Parsi', 'Pizza', 'Poke', 'Rajasthani', 'Rating', 'Raw Meats',\n",
      "       'Restaurant', 'Reviews', 'Roast Chicken', 'Rolls', 'Salad', 'Sandwich',\n",
      "       'Seafood', 'South Indian', 'Spanish', 'Steak', 'Street Food', 'Sushi',\n",
      "       'Tea', 'Thai', 'Tibetan', 'Turkish', 'Vietnamese', 'Votes', 'Wraps',\n",
      "       'avg_restaurant_Rating', 'avg_restaurant_Reviews',\n",
      "       'avg_restaurant_Votes', 'new_city', 'total_branches',\n",
      "       'votes_review_rating'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "((7763, 99), (7763,), (3328, 99), (3328,))"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Splitting the data to local train and test\n",
    "not_in_testset = ['Tex-Mex', 'Tamil', 'Sri Lankan', 'South American', 'Portuguese', \n",
    "                  'Nepalese', 'Konkan', 'Israeli', 'Indonesian', 'Greek', 'Gujarati', \n",
    "                  'Charcoal Chicken', 'Cantonese', 'Bubble Tea','Bohri']\n",
    "\n",
    "indep = DF_prod.columns.difference([dep_column\n",
    "                                    ,'Cusine_len'\n",
    "                                    , 'median_restaurant_Rating'\n",
    "                                    , 'median_restaurant_Reviews'\n",
    "                                    , 'median_restaurant_Votes'\n",
    "                                    , 'restaurant_type'\n",
    "                                    , 'Location'\n",
    "                                    ] \n",
    "                                   + not_in_testset\n",
    "                                  )\n",
    "\n",
    "\n",
    "\n",
    "#print(np.sort(indep).tolist())\n",
    "print(indep)\n",
    "\n",
    "np.random.seed(100)\n",
    "train_local_X, test_local_X, train_local_Y, test_local_Y = train_test_split(train_prod[indep], \n",
    "                                                                            train_prod[dep_column],\n",
    "                                                                            test_size = 0.3)\n",
    "\n",
    "train_local_X.shape, train_local_Y.shape, test_local_X.shape, test_local_Y.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Model-2 Validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.8203125\n"
     ]
    }
   ],
   "source": [
    "np.random.seed(100)\n",
    "RF = RandomForestClassifier(n_estimators = 220, n_jobs = -1)\n",
    "RF.fit(train_local_X, train_local_Y)\n",
    "RF_local_prediction = RF.predict(test_local_X)\n",
    "\n",
    "print(accuracy_score(RF_local_prediction, test_local_Y))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# # Model-2 Final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.random.seed(100)\n",
    "RF = RandomForestClassifier(n_estimators=220)\n",
    "RF.fit(train_prod[indep], train_prod[dep_column])\n",
    "RF_prod_prediction_20 = RF.predict_proba(test_prod[indep])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ensemble"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "ensemble = np.argmax(np.mean([RF_prod_prediction_9\n",
    "                              ,RF_prod_prediction_20\n",
    "                             ], axis = 0), axis = 1)\n",
    "\n",
    "Ensemble_prod_prediction = Dep_encoder.inverse_transform(ensemble)\n",
    "\n",
    "sub = pd.DataFrame({'Delivery_Time' : Ensemble_prod_prediction})\n",
    "sub.to_excel('sub_ENS_RF9_RF20_test.xlsx', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
